import os
import re
import csv
import sqlite3
from datetime import datetime
from flask import Flask, render_template, request, jsonify, g, send_file, redirect, url_for, session, after_this_request
from flask_babel import Babel, gettext as _
import tempfile
import subprocess
import logging
from logging.handlers import RotatingFileHandler
import codecs
import niantie_api
from websocket_handlers import socketio, init_socketio
import json
import websocket
import threading
import time
from flask_cors import CORS

# 加载配置
with open('config.json', 'r') as f:
    config = json.load(f)

# 配置日志
logging.basicConfig(
    level=getattr(logging, config['logging']['level']),
    format=config['logging']['format'],
    filename=config['logging']['file']
)
logger = logging.getLogger(__name__)

# 创建Flask应用
app = Flask(__name__)
app.config['SECRET_KEY'] = os.urandom(24)
app.config['SOCKETIO'] = config['socketio']  # 添加Socket.IO配置
CORS(app, resources={r"/api/*": {"origins": config['server']['cors_origins']}})

# 初始化Socket.IO
init_socketio(app)

# 注册蓝图
app.register_blueprint(niantie_api.bp)

# 设置 session secret key
app.secret_key = 'your-secret-key-here'  # 使用固定的 secret key
app.config['SESSION_TYPE'] = 'filesystem'
app.config['PERMANENT_SESSION_LIFETIME'] = 3600  # session 过期时间为1小时

# 支持的语言列表
LANGUAGES = {
    'zh': '中文',
    'vi': 'Tiếng Việt',
    'th': 'ไทย'
}

# 配置多语言支持
app.config['BABEL_DEFAULT_LOCALE'] = 'zh'
app.config['BABEL_TRANSLATION_DIRECTORIES'] = 'translations'
app.config['LANGUAGES'] = LANGUAGES

def get_locale():
    app.logger.info('开始获取语言设置')
    app.logger.info(f'当前 session: {session}')
    app.logger.info(f'请求参数: {request.args}')
    app.logger.info(f'请求路径: {request.path}')
    app.logger.info(f'请求方法: {request.method}')
    app.logger.info(f'Accept-Language: {request.headers.get("Accept-Language")}')
    
    # 从URL参数获取语言
    lang = request.args.get('lang')
    if lang in LANGUAGES:
        app.logger.info(f'从URL获取到语言设置: {lang}')
        session.permanent = True  # 使 session 持久化
        session['lang'] = lang
        app.logger.info(f'已将语言 {lang} 保存到 session 中')
        return lang
    
    # 从session中获取语言设置
    if 'lang' in session and session['lang'] in LANGUAGES:
        app.logger.info(f'从session获取到语言设置: {session["lang"]}')
        return session['lang']
    
    # 从浏览器首选项获取语言，并保存到session中
    browser_lang = request.accept_languages.best_match(LANGUAGES.keys())
    if browser_lang in LANGUAGES:
        app.logger.info(f'从浏览器获取到语言设置: {browser_lang}')
        session.permanent = True  # 使 session 持久化
        session['lang'] = browser_lang
        app.logger.info(f'已将浏览器语言 {browser_lang} 保存到 session 中')
        return browser_lang
    
    # 如果都没有找到合适的语言，使用默认语言
    default_lang = app.config['BABEL_DEFAULT_LOCALE']
    app.logger.info(f'使用默认语言设置: {default_lang}')
    session.permanent = True  # 使 session 持久化
    session['lang'] = default_lang
    app.logger.info(f'已将默认语言 {default_lang} 保存到 session 中')
    return default_lang

# 配置日志
def setup_logging():
    try:
        # 创建logs目录
        log_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'logs')
        if not os.path.exists(log_dir):
            os.makedirs(log_dir, mode=0o755, exist_ok=True)

        # 设置日志文件路径
        log_file = os.path.join(log_dir, 'app.log')
        
        # 配置日志处理器
        handler = RotatingFileHandler(
            log_file,
            maxBytes=10000,
            backupCount=3,
            encoding='utf-8',
            delay=True  # 延迟创建文件，直到第一次写入
        )
        handler.setLevel(logging.INFO)
        formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
        handler.setFormatter(formatter)
        
        # 配置应用日志
        app.logger.addHandler(handler)
        app.logger.setLevel(logging.INFO)
        
        # 确保日志文件存在并可写
        if os.path.exists(log_file):
            os.chmod(log_file, 0o666)
        
        app.logger.info('Logging system initialized')
        
    except Exception as e:
        print(f"Error setting up logging: {e}")
        # 如果完全无法设置日志，使用标准输出
        logging.basicConfig(level=logging.INFO)
        app.logger.addHandler(logging.StreamHandler())
        app.logger.warning('Falling back to console logging')

# 在应用启动时设置日志
setup_logging()

@app.before_request
def log_request_info():
    app.logger.info('-------------------')
    app.logger.info(f'请求路径: {request.path}')
    app.logger.info(f'请求方法: {request.method}')
    app.logger.info(f'请求参数: {request.args}')
    app.logger.info(f'当前session: {session}')
    app.logger.info(f'Accept-Language: {request.headers.get("Accept-Language")}')
    app.logger.info('-------------------')

# 初始化Babel
babel = Babel(app, locale_selector=get_locale)

# 添加模板全局变量
@app.context_processor
def inject_globals():
    return {
        'get_locale': get_locale,
        'LANGUAGES': LANGUAGES,
        '_': _  # 添加 gettext 函数到模板全局变量
    }

# 在每个请求之前设置语言
@app.before_request
def before_request():
    g.locale = str(get_locale())  # 确保语言代码是字符串
    app.logger.info(f'设置当前请求的语言为: {g.locale}')

# 配置
DATABASE = os.path.join(os.path.dirname(__file__), 'packages.db')
LITESTREAM_PATH = "C:\\Litestream\\litestream.exe"  # Litestream可执行文件路径
LOG_FILE = os.path.join(os.path.dirname(__file__), 'app.log')

def get_db():
    """获取数据库连接"""
    if 'db' not in g:
        g.db = sqlite3.connect(DATABASE)
        g.db.row_factory = sqlite3.Row
    return g.db

@app.teardown_appcontext
def close_db(error):
    """关闭数据库连接"""
    db = g.pop('db', None)
    if db is not None:
        db.close()

# 初始化数据库
def init_db():
    with app.app_context():
        db = get_db()
        with open('schema.sql', 'r', encoding='utf-8') as f:
            db.cursor().executescript(f.read())
        db.commit()

# 数据库初始化命令
@app.cli.command('init-db')
def init_db_command():
    """初始化数据库"""
    init_db()
    app.logger.info('数据库初始化完成')

# 确保数据库连接
@app.before_request
def setup():
    if not os.path.exists(DATABASE):
        app.logger.warning('数据库不存在，请先运行 flask init-db 命令')

# 记录扫描日志
def log_scan(package_id, scan_time, batch_id, status):
    db = get_db()
    cursor = db.cursor()
    try:
        cursor.execute('''
            INSERT INTO scan_logs (package_id, scan_time, batch_id, status)
            VALUES (?, ?, ?, ?)
        ''', (package_id, scan_time.strftime("%Y-%m-%d %H:%M:%S"), batch_id, status))
        db.commit()
        app.logger.info(f'记录扫描日志: {package_id}, {batch_id}, {status}')
    except Exception as e:
        app.logger.error(f'记录扫描日志失败: {str(e)}')
        db.rollback()

# 同步数据库
def sync_database():
    try:
        subprocess.Popen([LITESTREAM_PATH, "replicate", "-config", "litestream.yml"])
        app.logger.info('数据库同步已启动')
        return True
    except Exception as e:
        app.logger.error(f'启动数据库同步失败: {str(e)}')
        return False

# 恢复数据库
def restore_database():
    try:
        subprocess.Popen([LITESTREAM_PATH, "restore", "-config", "litestream.yml", DATABASE])
        app.logger.info('数据库恢复已启动')
        return True
    except Exception as e:
        app.logger.error(f'启动数据库恢复失败: {str(e)}')
        return False

# 路由
@app.route('/')
def index():
    return redirect(url_for('scan'))

@app.route('/scan')
def scan():
    return render_template('scan.html')

@app.route('/batch')
def batch():
    return render_template('batch.html')

@app.route('/report')
def report():
    return render_template('report.html')

@app.route('/txconfig')
def txconfig():
    return render_template('txconfig.html')

# API路由
@app.route('/api/batches', methods=['GET'])
def get_batches():
    """获取所有批次信息"""
    db = get_db()
    cursor = db.cursor()
    try:
        cursor.execute('''
            SELECT b.batch_id, b.create_time, b.status, 
                   COUNT(p.package_id) as total_packages,
                   SUM(CASE WHEN p.status = 'scanned' THEN 1 ELSE 0 END) as scanned_packages
            FROM batches b
            LEFT JOIN packages p ON b.batch_id = p.batch_id
            GROUP BY b.batch_id
            ORDER BY b.create_time DESC
        ''')
        batches = cursor.fetchall()
        result = []
        for batch in batches:
            total = batch['total_packages'] or 0
            scanned = batch['scanned_packages'] or 0
            completion = round(scanned / total * 100, 1) if total > 0 else 0
            result.append({
                'batch_id': batch['batch_id'],
                'create_time': batch['create_time'],
                'status': batch['status'],
                'total_packages': total,
                'scanned_packages': scanned,
                'completion': completion
            })
        return jsonify(result)
    except Exception as e:
        app.logger.error(f'获取批次信息失败: {str(e)}')
        return jsonify({'error': str(e)}), 500

@app.route('/api/batches', methods=['POST'])
def create_batch():
    """创建新批次"""
    data = request.json
    batch_id = data.get('batch_id')
    
    if not batch_id:
        return jsonify({'error': '批次ID不能为空'}), 400
        
    db = get_db()
    cursor = db.cursor()
    
    try:
        # 检查批次ID是否已存在
        cursor.execute('SELECT batch_id FROM batches WHERE batch_id = ?', (batch_id,))
        if cursor.fetchone():
            return jsonify({'error': f'批次 {batch_id} 已存在'}), 400
            
        # 获取当前时间
        now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        # 创建新批次
        cursor.execute('''
            INSERT INTO batches (batch_id, create_time, status, total_count, scanned_count, update_time)
            VALUES (?, ?, ?, 0, 0, ?)
        ''', (batch_id, now, '活跃', now))
        
        db.commit()
        return jsonify({
            'message': f'批次 {batch_id} 创建成功',
            'batch': {
                'batch_id': batch_id,
                'create_time': now,
                'status': '活跃',
                'total_count': 0,
                'scanned_count': 0,
                'update_time': now
            }
        })
        
    except Exception as e:
        db.rollback()
        app.logger.error(f'创建批次失败: {str(e)}')
        return jsonify({'error': str(e)}), 500

@app.route('/api/batches/<batch_id>', methods=['DELETE'])
def delete_batch(batch_id):
    """删除批次"""
    db = get_db()
    cursor = db.cursor()
    try:
        # 先删除批次下的所有包裹
        cursor.execute('DELETE FROM packages WHERE batch_id = ?', (batch_id,))
        # 再删除批次本身
        cursor.execute('DELETE FROM batches WHERE batch_id = ?', (batch_id,))
        db.commit()
        app.logger.info(f'删除批次: {batch_id}')
        return jsonify({'message': f'批次 {batch_id} 删除成功'})
    except Exception as e:
        db.rollback()
        app.logger.error(f'删除批次失败: {str(e)}')
        return jsonify({'error': str(e)}), 500

@app.route('/api/packages', methods=['GET'])
def get_packages():
    """获取包裹列表"""
    batch_id = request.args.get('batch_id')
    package_id = request.args.get('package_id')
    status = request.args.get('status')  # 添加状态筛选参数
    
    db = get_db()
    cursor = db.cursor()
    
    try:
        query = '''
            SELECT p.*, b.status as batch_status
            FROM packages p
            JOIN batches b ON p.batch_id = b.batch_id
            WHERE 1=1
        '''
        params = []
        
        # 按批次ID筛选
        if batch_id and batch_id != '全部':
            query += ' AND p.batch_id = ?'
            params.append(batch_id)
            
        # 按包裹ID筛选
        if package_id:
            query += ' AND p.package_id = ?'
            params.append(package_id)
            
        # 按状态筛选
        if status:
            if status == '已扫描':
                query += ' AND p.status = "scanned"'
            elif status == '未扫描':
                query += ' AND p.status = "unscanned"'
            
        query += ' ORDER BY p.create_time DESC'
        
        app.logger.info(f'SQL Query: {query}')  # 添加日志
        app.logger.info(f'Parameters: {params}')  # 添加日志
        
        cursor.execute(query, params)
        packages = cursor.fetchall()
        result = []
        
        for package in packages:
            # 转换状态为中文并添加颜色标识
            status = '已扫描' if package['status'] == 'scanned' else '未扫描'
            status_color = 'green' if package['status'] == 'scanned' else 'red'
            
            result.append({
                'package_id': package['package_id'],
                'batch_id': package['batch_id'],
                'status': status,
                'status_color': status_color,
                'first_scan': package['first_scan'],
                'last_scan': package['last_scan'],
                'operator': package['operator'],
                'create_time': package['create_time'],
                'batch_status': package['batch_status']
            })
        
        app.logger.info(f'Found {len(result)} packages')  # 添加日志
        
        # 构建命中信息
        hit_info = {
            'current_package': package_id or '无',
            'hit_batch': batch_id if result else '无',
            'total_hits': len(result)
        }
        
        # 如果是通过 package_id 查询，返回带命中信息的响应
        if package_id:
            return jsonify({
                'packages': result,
                'hit_info': hit_info
            })
        
        # 否则返回兼容格式的包裹列表
        return jsonify(result)
        
    except Exception as e:
        app.logger.error(f'获取包裹列表失败: {str(e)}')
        return jsonify({'error': str(e)}), 500

@app.route('/api/packages/<package_id>', methods=['DELETE'])
def delete_package(package_id):
    """删除包裹"""
    db = get_db()
    cursor = db.cursor()
    try:
        cursor.execute('DELETE FROM packages WHERE package_id = ?', (package_id,))
        db.commit()
        app.logger.info(f'删除包裹: {package_id}')
        return jsonify({'message': f'包裹 {package_id} 删除成功'})
    except Exception as e:
        db.rollback()
        app.logger.error(f'删除包裹失败: {str(e)}')
        return jsonify({'error': str(e)}), 500

@app.route('/api/scan', methods=['POST'])
def process_scan():
    """处理包裹扫描"""
    data = request.json
    if not data:
        return jsonify({'error': '无效的请求数据'}), 400
        
    package_id = data.get('package_id')
    batch_id = data.get('batch_id')
    
    if not package_id or not batch_id:
        return jsonify({'error': '包裹ID和批次ID不能为空'}), 400
        
    db = get_db()
    cursor = db.cursor()
    
    try:
        # 检查批次是否存在且未关闭
        cursor.execute('SELECT status FROM batches WHERE batch_id = ?', (batch_id,))
        batch = cursor.fetchone()
        if not batch:
            return jsonify({'error': '批次不存在'}), 404
        if batch['status'] == 'closed':
            return jsonify({'error': '批次已关闭'}), 400
            
        # 获取当前时间
        now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        # 检查包裹是否存在
        cursor.execute('SELECT * FROM packages WHERE package_id = ? AND batch_id = ?', 
                      (package_id, batch_id))
        package = cursor.fetchone()
        
        if not package:
            return jsonify({'error': '包裹不存在或不属于指定批次'}), 404
            
        # 更新包裹状态
        if package['status'] == 'unscanned':
            cursor.execute('''
                UPDATE packages 
                SET status = 'scanned', 
                    first_scan = ?,
                    last_scan = ?
                WHERE package_id = ? AND batch_id = ?
            ''', (now, now, package_id, batch_id))
        else:
            cursor.execute('''
                UPDATE packages 
                SET last_scan = ?
                WHERE package_id = ? AND batch_id = ?
            ''', (now, package_id, batch_id))
            
        # 更新批次统计
        cursor.execute('''
            UPDATE batches 
            SET scanned_count = (
                SELECT COUNT(*) FROM packages 
                WHERE batch_id = ? AND status = 'scanned'
            ),
            update_time = ?
            WHERE batch_id = ?
        ''', (batch_id, now, batch_id))
        
        # 记录扫描日志
        cursor.execute('''
            INSERT INTO scan_logs (package_id, scan_time, batch_id, status)
            VALUES (?, ?, ?, ?)
        ''', (package_id, now, batch_id, 'scanned'))
        
        db.commit()
        
        # 获取更新后的包裹和批次信息
        cursor.execute('''
            SELECT p.*, b.total_count, b.scanned_count
            FROM packages p
            JOIN batches b ON p.batch_id = b.batch_id
            WHERE p.package_id = ? AND p.batch_id = ?
        ''', (package_id, batch_id))
        
        updated_package = cursor.fetchone()
        total_count = updated_package['total_count']
        scanned_count = updated_package['scanned_count']
        completion = round(scanned_count / total_count * 100, 1) if total_count > 0 else 0
        
        # 转换状态为中文
        status = '已扫描' if updated_package['status'] == 'scanned' else '未扫描'
        
        return jsonify({
            'success': True,
            'message': '扫描成功',
            'package_id': package_id,
            'matched_batch': batch_id,
            'package': {
                'package_id': updated_package['package_id'],
                'batch_id': updated_package['batch_id'],
                'status': status,
                'first_scan': updated_package['first_scan'],
                'last_scan': updated_package['last_scan'],
                'operator': updated_package['operator']
            },
            'batch_stats': {
                'total': total_count,
                'scanned': scanned_count,
                'completion': completion
            }
        })
        
    except Exception as e:
        db.rollback()
        app.logger.error(f'扫描失败: {str(e)}')
        return jsonify({'error': str(e)}), 500

@app.route('/api/import', methods=['POST'])
def import_packages():
    """导入包裹数据"""
    if 'file' not in request.files and 'data' not in request.json:
        return jsonify({'error': '没有提供文件或数据'}), 400
    
    batch_id = request.form.get('batch_id') if 'file' in request.files else request.json.get('batch_id')
    if not batch_id:
        return jsonify({'error': '批次ID不能为空'}), 400
    
    db = get_db()
    cursor = db.cursor()
    
    # 检查批次是否存在
    cursor.execute('SELECT 1 FROM batches WHERE batch_id = ?', (batch_id,))
    if not cursor.fetchone():
        return jsonify({'error': f'批次 {batch_id} 不存在'}), 400
    
    # 解析包裹号
    package_ids = []
    if 'file' in request.files:
        file = request.files['file']
        content = file.read().decode('utf-8').splitlines()
        for line in content:
            line = line.strip()
            if line:
                package_ids.append(line)
    else:
        data = request.json.get('data', '')
        lines = data.splitlines()
        for line in lines:
            line = line.strip()
            if line:
                match = re.search(r'^([A-Za-z0-9]+)', line)
                if match:
                    package_ids.append(match.group(1))
    
    # 去重
    package_ids = list(set(package_ids))
    
    # 获取已存在的包裹
    if package_ids:
        placeholders = ','.join('?' * len(package_ids))
        cursor.execute(f'''
            SELECT package_id, batch_id, status, first_scan, last_scan, operator
            FROM packages
            WHERE package_id IN ({placeholders})
        ''', package_ids)
        
        existing_packages = {}
        for row in cursor.fetchall():
            if row['package_id'] not in existing_packages:
                existing_packages[row['package_id']] = row
    else:
        existing_packages = {}
    
    # 导入包裹
    new_count = 0
    moved_count = 0
    try:
        for package_id in package_ids:
            if package_id in existing_packages:
                existing_pkg = existing_packages[package_id]
                if existing_pkg['batch_id'] != batch_id:
                    # 移动包裹到当前批次
                    cursor.execute('''
                        UPDATE packages 
                        SET batch_id = ?,
                            status = ?,
                            first_scan = ?,
                            last_scan = ?,
                            operator = ?,
                            create_time = ?
                        WHERE package_id = ?
                    ''', (batch_id, existing_pkg['status'], existing_pkg['first_scan'], 
                         existing_pkg['last_scan'], existing_pkg['operator'], 
                         datetime.now().strftime('%Y-%m-%d %H:%M:%S'), package_id))
                    moved_count += 1
                continue
            
            cursor.execute('''
                INSERT INTO packages (package_id, batch_id, status, first_scan, last_scan, operator, create_time)
                VALUES (?, ?, ?, NULL, NULL, ?, ?)
            ''', (package_id, batch_id, 'unscanned', '系统导入', datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
            new_count += 1
        
        # 更新批次的总数
        cursor.execute('''
            UPDATE batches 
            SET total_count = (
                SELECT COUNT(*) FROM packages 
                WHERE batch_id = ?
            ),
            update_time = ?
            WHERE batch_id = ?
        ''', (batch_id, datetime.now().strftime('%Y-%m-%d %H:%M:%S'), batch_id))
        
        db.commit()
        app.logger.info(f'导入包裹: 批次={batch_id}, 新增={new_count}, 移动={moved_count}')
        return jsonify({
            'message': f'导入成功: 新增 {new_count} 个包裹, 移动 {moved_count} 个包裹',
            'new_count': new_count,
            'moved_count': moved_count
        })
    except Exception as e:
        db.rollback()
        app.logger.error(f'导入包裹失败: {str(e)}')
        return jsonify({'error': str(e)}), 500

@app.route('/api/match', methods=['POST'])
def match_packages():
    data = request.get_json()
    package_ids = data.get('package_ids', [])
    batch_id = data.get('batch_id')
    
    if not package_ids or not batch_id:
        return jsonify({'error': '缺少必要参数'}), 400
        
    db = get_db()
    cursor = db.cursor()
    
    # 查找已存在的包裹
    placeholders = ','.join(['?' for _ in package_ids])
    cursor.execute(
        f'SELECT package_id, batch_id FROM packages WHERE package_id IN ({placeholders})',
        package_ids
    )
    existing_packages = {row[0]: row[1] for row in cursor.fetchall()}
    
    # 初始化结果统计
    results = {
        'matched': [],  # 匹配到的包裹
        'not_found': []  # 未找到的包裹
    }
    
    # 处理每个包裹
    scan_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    for package_id in package_ids:
        if package_id in existing_packages:
            # 包裹存在，更新其状态为"已扫描"
            cursor.execute('''
                UPDATE packages 
                SET status = 'scanned',
                    last_scan = ?,
                    operator = ?
                WHERE package_id = ?
            ''', (scan_time, '系统匹配', package_id))
            results['matched'].append({
                'package_id': package_id,
                'original_batch': existing_packages[package_id]
            })
        else:
            results['not_found'].append(package_id)
    
    db.commit()
    
    # 返回匹配结果
    return jsonify({
        'message': f"匹配完成。找到 {len(results['matched'])} 个包裹，{len(results['not_found'])} 个包裹未找到。",
        'results': results
    })

@app.route('/api/export', methods=['POST'])
def export_report():
    """导出报表"""
    data = request.json
    batch_id = data.get('batch_id')
    status = data.get('status')
    
    db = get_db()
    cursor = db.cursor()
    
    query = "SELECT * FROM packages"
    params = []
    
    if batch_id and batch_id != "全部":
        query += " WHERE batch_id = ?"
        params.append(batch_id)
        
        if status and status != "全部":
            query += " AND status = ?"
            params.append('scanned' if status == '已扫描' else 'unscanned')
    elif status and status != "全部":
        query += " WHERE status = ?"
        params.append('scanned' if status == '已扫描' else 'unscanned')
    
    try:
        cursor.execute(query, params)
        packages = cursor.fetchall()
        
        # 创建临时CSV文件
        temp_dir = tempfile.gettempdir()
        temp_path = os.path.join(temp_dir, 'packages_report.csv')
        
        with open(temp_path, 'w', newline='', encoding='utf-8-sig') as f:
            writer = csv.writer(f)
            # 写入表头
            writer.writerow(['包裹单号', '批次', '状态', '首次扫描', '最后扫描', '操作员'])
            # 写入数据
            for pkg in packages:
                status = '已扫描' if pkg['status'] == 'scanned' else '未扫描'
                writer.writerow([
                    pkg['package_id'],
                    pkg['batch_id'],
                    status,
                    pkg['first_scan'] or '',
                    pkg['last_scan'] or '',
                    pkg['operator'] or ''
                ])
        
        app.logger.info(f'导出报表: 批次={batch_id}, 状态={status}, 记录数={len(packages)}')
        
        @after_this_request
        def remove_file(response):
            try:
                os.remove(temp_path)
                app.logger.info(f'临时文件已删除: {temp_path}')
            except Exception as e:
                app.logger.error(f'删除临时文件失败: {str(e)}')
            return response
        
        return send_file(
            temp_path,
            mimetype='text/csv',
            as_attachment=True,
            download_name='packages_report.csv'
        )
        
    except Exception as e:
        app.logger.error(f'导出报表失败: {str(e)}')
        return jsonify({'error': str(e)}), 500

@app.route('/api/sync', methods=['POST'])
def sync_db():
    """同步数据库"""
    if sync_database():
        return jsonify({'message': '数据库同步已启动'})
    else:
        return jsonify({'error': '启动数据库同步失败'}), 500

@app.route('/api/restore', methods=['POST'])
def restore_db():
    """恢复数据库"""
    if restore_database():
        return jsonify({'message': '数据库恢复已启动'})
    else:
        return jsonify({'error': '启动数据库恢复失败'}), 500

def get_communication_config():
    """获取通信配置"""
    db = get_db()
    config = db.execute('SELECT * FROM communication_config WHERE id = 1').fetchone()
    if config:
        return dict(config)
    return None

def update_communication_config(config_type, config_data):
    """更新通信配置"""
    db = get_db()
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    if config_type == 'websocket':
        db.execute('''
            UPDATE communication_config 
            SET server_url = ?, 
                port = ?, 
                protocol = ?, 
                reconnect_interval = ?, 
                max_retries = ?, 
                timeout = ?, 
                enabled = ?,
                updated_at = ?
            WHERE id = 1
        ''', (
            config_data['server_url'],
            config_data['port'],
            config_data['protocol'],
            config_data['reconnect_interval'],
            config_data['max_retries'],
            config_data['timeout'],
            config_data['enabled'],
            current_time
        ))
    elif config_type == 'webrtc':
        db.execute('''
            UPDATE communication_config 
            SET webrtc_enabled = ?,
                webrtc_ice_servers = ?,
                webrtc_peer_connection_config = ?,
                webrtc_data_channel_config = ?,
                updated_at = ?
            WHERE id = 1
        ''', (
            config_data['webrtc_enabled'],
            json.dumps(config_data['webrtc_ice_servers']),
            json.dumps(config_data['webrtc_peer_connection_config']),
            json.dumps(config_data['webrtc_data_channel_config']),
            current_time
        ))
    
    db.commit()
    return get_communication_config()

def update_connection_status(config_type, connected, error=None):
    """更新连接状态"""
    db = get_db()
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    if connected:
        if config_type == 'websocket':
            db.execute('''
                UPDATE communication_config 
                SET ws_last_connected = ?,
                    ws_last_error = NULL
                WHERE id = 1
            ''', (current_time,))
        else:
            db.execute('''
                UPDATE communication_config 
                SET rtc_last_connected = ?,
                    rtc_last_error = NULL
                WHERE id = 1
            ''', (current_time,))
    else:
        if config_type == 'websocket':
            db.execute('''
                UPDATE communication_config 
                SET ws_last_error = ?
                WHERE id = 1
            ''', (error,))
        else:
            db.execute('''
                UPDATE communication_config 
                SET rtc_last_error = ?
                WHERE id = 1
            ''', (error,))
    
    db.commit()

@app.route('/api/communication/config', methods=['GET', 'POST'])
def handle_communication_config():
    """处理通信配置"""
    if request.method == 'GET':
        config = get_communication_config()
        if config:
            # 解析 JSON 字符串为 Python 对象
            if config['webrtc_ice_servers']:
                config['webrtc_ice_servers'] = json.loads(config['webrtc_ice_servers'])
            if config['webrtc_peer_connection_config']:
                config['webrtc_peer_connection_config'] = json.loads(config['webrtc_peer_connection_config'])
            if config['webrtc_data_channel_config']:
                config['webrtc_data_channel_config'] = json.loads(config['webrtc_data_channel_config'])
            return jsonify({'success': True, 'config': config})
        return jsonify({'success': False, 'message': '配置不存在'})
    
    elif request.method == 'POST':
        try:
            data = request.json
            config_type = data.get('type')
            config_data = data.get('config')
            
            if not config_type or not config_data:
                return jsonify({'success': False, 'message': '无效的请求数据'})
            
            if config_type not in ['websocket', 'webrtc']:
                return jsonify({'success': False, 'message': '无效的配置类型'})
            
            # 获取数据库连接
            db = get_db()
            cursor = db.cursor()
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            try:
                if config_type == 'websocket':
                    # 更新WebSocket配置
                    cursor.execute('''
                        INSERT OR REPLACE INTO communication_config (
                            id, server_url, port, protocol, 
                            reconnect_interval, max_retries, timeout, 
                            enabled, updated_at
                        ) VALUES (
                            1, ?, ?, ?, ?, ?, ?, ?, ?
                        )
                    ''', (
                        config_data['server_url'],
                        config_data['port'],
                        config_data.get('protocol', 'ws'),
                        config_data.get('reconnect_interval', 5000),
                        config_data.get('max_retries', 3),
                        config_data.get('timeout', 5000),
                        config_data.get('enabled', True),
                        current_time
                    ))
                elif config_type == 'webrtc':
                    # 更新WebRTC配置
                    cursor.execute('''
                        UPDATE communication_config 
                        SET webrtc_enabled = ?,
                            webrtc_ice_servers = ?,
                            webrtc_peer_connection_config = ?,
                            webrtc_data_channel_config = ?,
                            updated_at = ?
                        WHERE id = 1
                    ''', (
                        config_data.get('webrtc_enabled', False),
                        json.dumps(config_data.get('webrtc_ice_servers', [])),
                        json.dumps(config_data.get('webrtc_peer_connection_config', {})),
                        json.dumps(config_data.get('webrtc_data_channel_config', {})),
                        current_time
                    ))
                
                db.commit()
                return jsonify({'success': True, 'message': '配置保存成功'})
                
            except sqlite3.Error as e:
                db.rollback()
                logger.error(f'数据库错误: {str(e)}')
                return jsonify({'success': False, 'message': f'保存配置失败: {str(e)}'})
                
        except Exception as e:
            logger.error(f'处理配置请求失败: {str(e)}')
            return jsonify({'success': False, 'message': f'处理请求失败: {str(e)}'})

def test_websocket_connection(config):
    """测试 WebSocket 连接"""
    try:
        url = f"{config['protocol']}://{config['server_url']}:{config['port']}"
        ws = websocket.WebSocket()
        ws.settimeout(config.get('timeout', 5000) / 1000)  # 转换为秒
        
        # 连接 WebSocket 服务器
        ws.connect(url)
        
        # 发送测试消息
        ws.send(json.dumps({'type': 'ping'}))
        
        # 等待响应
        response = ws.recv()
        
        # 关闭连接
        ws.close()
        
        # 更新连接状态
        update_connection_status('websocket', True)
        
        return True, None
        
    except websocket.WebSocketTimeoutException:
        error = '连接超时'
        update_connection_status('websocket', False, error)
        return False, error
        
    except websocket.WebSocketConnectionClosedException:
        error = '连接被关闭'
        update_connection_status('websocket', False, error)
        return False, error
        
    except websocket.WebSocketBadStatusException as e:
        error = f'服务器返回错误状态: {e.status_code}'
        update_connection_status('websocket', False, error)
        return False, error
        
    except Exception as e:
        error = str(e)
        update_connection_status('websocket', False, error)
        return False, error

def test_webrtc_connection(config):
    """测试 WebRTC 连接"""
    try:
        if not config.get('webrtc_enabled'):
            return False, 'WebRTC 未启用'
            
        # 验证 ICE 服务器配置
        ice_servers = config.get('webrtc_ice_servers', [])
        if not ice_servers:
            return False, '未配置 ICE 服务器'
            
        # 验证每个 ICE 服务器的 URL
        for server in ice_servers:
            if not server.get('urls'):
                return False, 'ICE 服务器缺少 URL'
                
        # 验证对等连接配置
        peer_config = config.get('webrtc_peer_connection_config', {})
        if not isinstance(peer_config, dict):
            return False, '无效的对等连接配置'
            
        # 验证数据通道配置
        channel_config = config.get('webrtc_data_channel_config', {})
        if not isinstance(channel_config, dict):
            return False, '无效的数据通道配置'
            
        # 更新连接状态
        update_connection_status('webrtc', True)
        
        return True, None
        
    except Exception as e:
        error = str(e)
        update_connection_status('webrtc', False, error)
        return False, error

@app.route('/api/communication/test', methods=['POST'])
def test_communication():
    """测试通信连接"""
    try:
        data = request.json
        config_type = data.get('type')
        config = data.get('config')
        
        if not config_type or not config:
            return jsonify({'success': False, 'message': '无效的请求数据'})
            
        if config_type == 'websocket':
            # 创建Socket.IO客户端进行测试
            try:
                from socketio import Client
                sio = Client()
                
                # 连接超时设置
                timeout = config.get('timeout', 5000) / 1000  # 转换为秒
                
                # 构建连接URL
                url = f"http://{config['server_url']}:{config.get('port', 5000)}"
                
                # 尝试连接
                sio.connect(url, wait_timeout=timeout)
                
                # 发送测试消息
                sio.emit('message', {'type': 'ping'})
                
                # 等待响应
                sio.sleep(1)
                
                # 断开连接
                sio.disconnect()
                
                return jsonify({
                    'success': True,
                    'message': '连接测试成功',
                    'connected': True
                })
                
            except Exception as e:
                error_message = str(e)
                return jsonify({
                    'success': False,
                    'message': f'连接测试失败: {error_message}',
                    'connected': False,
                    'last_error': error_message
                })
                
        elif config_type == 'webrtc':
            success, error = test_webrtc_connection(config)
            return jsonify({
                'success': success,
                'message': error if error else '连接测试成功',
                'connected': success,
                'last_error': error
            })
            
        else:
            return jsonify({'success': False, 'message': '无效的配置类型'})
            
    except Exception as e:
        logger.error(f'测试连接失败: {str(e)}')
        return jsonify({
            'success': False,
            'message': str(e),
            'connected': False,
            'last_error': str(e)
        })

@app.route('/api/communication/status')
def get_communication_status():
    """获取通信状态"""
    try:
        config = get_communication_config()
        if not config:
            return jsonify({'success': False, 'message': '配置不存在'})
            
        # WebSocket 状态
        ws_status = {
            'connected': False,
            'last_connected': config['ws_last_connected'],
            'last_error': config['ws_last_error']
        }
        
        # 如果启用了 WebSocket，检查连接状态
        if config['enabled']:
            try:
                url = f"{config['protocol']}://{config['server_url']}:{config['port']}"
                ws = websocket.WebSocket()
                ws.settimeout(1)  # 快速检查
                ws.connect(url)
                ws.close()
                ws_status['connected'] = True
                update_connection_status('websocket', True)
            except:
                ws_status['connected'] = False
        
        # WebRTC 状态
        rtc_status = {
            'connected': False,
            'last_connected': config['rtc_last_connected'],
            'last_error': config['rtc_last_error']
        }
        
        # 如果启用了 WebRTC，检查配置状态
        if config['webrtc_enabled']:
            try:
                ice_servers = json.loads(config['webrtc_ice_servers'])
                if ice_servers and isinstance(ice_servers, list):
                    rtc_status['connected'] = True
                    update_connection_status('webrtc', True)
            except:
                rtc_status['connected'] = False
        
        return jsonify({
            'success': True,
            'websocket': ws_status,
            'webrtc': rtc_status
        })
        
    except Exception as e:
        logger.error(f'获取状态失败: {str(e)}')
        return jsonify({'success': False, 'message': str(e)})

if __name__ == '__main__':
    host = config['server']['host']
    port = config['server']['port']
    debug = config['server']['debug']
    
    logger.info(f'Starting server on {host}:{port}')
    socketio.run(app, host=host, port=port, debug=debug, allow_unsafe_werkzeug=True)

